/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author 013v
 */
public class UserManager {

    public boolean checkUser(User newUser) {
        boolean result = false;

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            int i = 1;
            //CHENES baguhin yung ? into @something
            PreparedStatement pstat = conn.prepareStatement("SELECT * FROM user WHERE Username = ? AND Password = DES_ENCRYPT(?) AND Status = '1'");
            pstat.setString(i++, newUser.getUsername());
            pstat.setString(i++, newUser.getPassword());

            ResultSet rs = pstat.executeQuery();

            while (rs.next()) {
                result = true;
            }
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }


        return result;
    }

    public User getIDType(User newUser) {
        User result = null;

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String query = "SELECT u.AccountID, ARef.AccountTypeRef, u.Status FROM user u, usertype ARef where u.UserType = ARef.AccountTypeRef and Username=? and Password=DES_ENCRYPT(?)";
            PreparedStatement pstmt =
                    conn.prepareStatement(query);

            int i = 1;

            pstmt.setString(i++, newUser.getUsername());
            pstmt.setString(i++, newUser.getPassword());

            ResultSet rst = pstmt.executeQuery();

            result = new User();
            while (rst.next()) {
                result.setAccountID(rst.getString("u.AccountID"));
                result.setUserType(rst.getInt("ARef.AccountTypeRef"));
                result.setStatus(rst.getInt("u.Status"));
            }

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return result;
    }

    public boolean checkUserExists(String user) {
        boolean result = false;
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            PreparedStatement pstat = conn.prepareStatement("SELECT * FROM user WHERE Username = ?");
            pstat.setString(1, user);

            ResultSet rs = pstat.executeQuery();

            while (rs.next()) {
                result = true;
            }
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }


        return result;
    }

    public boolean checkEmailExists(String email) {
        boolean result = false;
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            //CHENES baguhin yung ? into @something
            PreparedStatement pstat = conn.prepareStatement("SELECT * FROM user WHERE Email = ?");
            pstat.setString(1, email);

            ResultSet rs = pstat.executeQuery();

            while (rs.next()) {
                result = true;
            }
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }


        return result;
    }

    public boolean addUser(User newUser) {
        boolean result = false;

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();
            int i = 1;

            String que = "INSERT into user(AccountID, FirstName, LastName, MiddleInitial, Username, Password, Email, UserType, Status) VALUES(?,?,?,?,?,?,?,?,?)";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(i++, newUser.getAccountID());
            pstmt.setString(i++, newUser.getFirstName());
            pstmt.setString(i++, newUser.getLastName());
            pstmt.setString(i++, newUser.getMiddleInitial());
            pstmt.setString(i++, newUser.getUsername());
            pstmt.setString(i++, newUser.getPassword());
            pstmt.setString(i++, newUser.getEmail());
            pstmt.setInt(i++, newUser.getUserType());
            pstmt.setString(i++, "1");

            pstmt.executeUpdate();
            //close DB connection
            conn.close();

            result = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return result;
    }

//addUserManager
//addManager
//editUserLock
//editUserUnlock
//addLocklog
//addUnlocklog
    public boolean timeLog(DateTime datetime) {
        boolean result = false;

        try {
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            PreparedStatement pstmt = conn.prepareStatement("INSERT "
                    + "INTO timelogin(AccountID,TimeLogin,DateLogin)"
                    + " values(?,CURTIME(),CURDATE())");

            pstmt.setString(1, datetime.getAccountID());

            pstmt.executeUpdate();

            result = true;
            conn.close();

        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return result;
    }

    public boolean timeLogout(DateTime datetime) {
        boolean result = false;

        try {
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("INSERT "
                    + "INTO timelogout(AccountID,TimeLogout,DateLogout)"
                    + " values(?,?,?)");

            pstmt.setString(i++, datetime.getAccountID());
            pstmt.setString(i++, datetime.getTime());
            pstmt.setString(i++, datetime.getDate());

            pstmt.executeUpdate();

            result = true;
            conn.close();

        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return result;
    }

    public boolean addUserIDTrack(User newUser, int newNum) {
        boolean result = false;

        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "INSERT into useridtrack(AccountID, FakeAccountID) VALUES(?,?)";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(1, newUser.getAccountID());
            pstmt.setInt(2, newNum);

            pstmt.executeUpdate();
            //close DB connection
            conn.close();

            result = true;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return result;
    }

    public int getOAccountID(int newNum) {
        int result = 0;
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT AccountID FROM useridtrack WHERE FakeAccountID = ?";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setInt(1, newNum);

            ResultSet res = pstmt.executeQuery();

            if (res.next()) {
                result = res.getInt("AccountID");
            }

            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return result;
    }

    public String getAccountIDFromUser(String user) {
        String result = null;
        try {
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT AccountID FROM user WHERE Username = ?";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(1, user);

            ResultSet res = pstmt.executeQuery();

            if (res.next()) {
                result = res.getString("AccountID");
            }

            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return result;
    }

    public User getUser(String newUser) {
        User result = null;
        try {

            String uid = getAccountIDFromUser(newUser);

            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            String que = "SELECT * FROM user u join useridtrack ut on u.AccountID = ut.AccountID where u.AccountID=?";
            PreparedStatement pstmt =
                    conn.prepareStatement(que);

            pstmt.setString(1, uid);

            ResultSet rst = pstmt.executeQuery();
            result = new User();
            while (rst.next()) {
                result.setAccountID(rst.getString("ut.FakeAccountID"));
                result.setUsername(rst.getString("u.Username"));
                result.setFirstName(rst.getString("u.FirstName"));
                result.setLastName(rst.getString("u.LastName"));
                result.setMiddleInitial(rst.getString("u.MiddleInitial"));
                result.setEmail(rst.getString("u.Email"));
            }

            conn.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public boolean editUserPassword(User newUser) {
        boolean result = false;

        try {
            //get connection
            DBConnection db = new DBConnection();
            Connection conn = db.getConnection();

            int i = 1;

            PreparedStatement pstmt = conn.prepareStatement("UPDATE user SET Password = ?, Status = 1 WHERE Username=?");

            pstmt.setString(i++, newUser.getPassword());
            pstmt.setString(i++, newUser.getUsername());
            pstmt.executeUpdate();

            result = true;
            conn.close();

        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return result;
    }
}
